package cn.com.callback

import cn.com.elite.EliteBean
import com.alibaba.fastjson.JSONObject
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * 网聊数据汇总定时任务
 * 2023/5/30
 */

public class apiHSTChatData3 implements ServiceBackInvoke {
    private static final Logger log = LoggerFactory.getLogger(apiHSTChatData3.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse httpServletResponse) {

        long startTime = System.currentTimeMillis();
        long endTime = System.currentTimeMillis();

        httpServletResponse.setCharacterEncoding("UTF-8");
        httpServletResponse.setContentType("application/json;charset=utf-8");
        JSONObject result = new JSONObject();

        log.debug("网聊数据汇总手动执行任务启动");
        try {
            String starttime = request.getParameter("starttime");
            String endtime = request.getParameter("endtime");
            log.debug("调用参数-开始时间:" + starttime + " 结束时间:" + endtime );
            String selectSql = "";
            /*String selectSql = "SELECT DISTINCT\n" +
                    "upper(\n" +
                    "UUID()) aaaaaaa,\n" +
                    "r1.id,\n" +
                    "r1.chat_session_id,\n" +
                    "r1.client_id,\n" +
                    "r2.queue_id,\n" +
                    "r3.isvip,\n" +
                    "ifnull( r4.start_time, 0 ) start_time,\n" +
                    "ifnull( r4.end_time, 0 ) end_time,\n" +
                    "ifnull(r5.robotid,'') robotid,\n" +
                    "ifnull( r5.robottime, 0 ) tttt,\n" +
                    "ifnull(r6.isagent,'')isagent ,\n" +
                    "r7.creation_time,\n" +
                    "ifnull(r7.agent_wait_time,0) agent_wait_time,\n" +
                    "ifnull(r6.agent_is_giveup,'') agent_is_giveup,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN r9.isleave_msg = '是' THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS isleave_msg,\n" +
                    "ifnull(a.to_chat_user_id,'') AS staff_id,\n" +
                    "ifnull(from_unixtime( b.maxtime / 1000, '%Y-%m-%d %H:%i:%s' ),'') AS maxtime,\n" +
                    "CASE\n" +
                    "WHEN c.istransfer > 0 THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS istransfer,\n" +
                    "ifnull(c.chat_session_id,'') AS zj_chat_session_id,\n" +
                    "ifnull(d.rg_service_duration,0) AS rg_service_duration,\n" +
                    "ifnull(e.cust_zrghsl,0) AS cust_zrghsl,\n" +
                    "ifnull(f.maxtotal,0) AS maxtotal,\n" +
                    "ifnull(f.mintotal,0) AS mintotal,\n" +
                    "ifnull(g.zx_send_sl,'') AS zx_send_sl,\n" +
                    "ifnull(h.是否邀评,'') as isyp,\n" +
                    "ifnull(i.是否评价,'') as israting,\n" +
                    "CASE\n" +
                    "i.评价满意度 \n" +
                    "WHEN 1 THEN\n" +
                    "'非常满意' \n" +
                    "WHEN 2 THEN\n" +
                    "'满意' \n" +
                    "WHEN 3 THEN\n" +
                    "'一般' \n" +
                    "WHEN 4 THEN\n" +
                    "'不满意' \n" +
                    "WHEN 5 THEN\n" +
                    "'非常不满意' ELSE '未评价' \n" +
                    "END AS rating,\n" +
                    "r8.custtotal,\n" +
                    "r8.response_time,\n" +
                    "ifnull( j.在线请求数, 0 ) as zxnum,\n" +
                    "ifnull( k.访客放弃数, 0 ) as fknum,\n" +
                    "ifnull( l.未等待应答数, 0 )as notwaitnum,\n" +
                    "ifnull( m.等待应答数, 0 )as waitnum,\n" +
                    "ifnull( n.在线应答数, 0 )as zxydnum,\n" +
                    "ifnull( o.等待放弃数, 0 )as waitfqnum,\n" +
                    "ifnull( p.等待转留言数, 0 )as waitlynum,\n" +
                    "ifnull( q.总等待数, 0 )as waittotal,\n" +
                    "ifnull(s.离线请求数,0)as lxnum\n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id,\n" +
                    "id,\n" +
                    "client_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id,\n" +
                    "id,\n" +
                    "client_id \n" +
                    "FROM\n" +
                    "( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, id, client_id FROM chat_request cr \n" +
                    "where cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "ORDER BY chat_session_id ASC, creation_time ASC ) t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC \n" +
                    ") r1\n" +
                    "LEFT JOIN ( SELECT chat_session_id, queue_id, id FROM chat_request cr WHERE  \n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    " AND length( from_chat_user_id )> 6 ORDER BY chat_session_id DESC ) r2 ON r1.id = r2.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cv.visitor_guid IS NOT NULL THEN\n" +
                    "'否' ELSE '是' \n" +
                    "END AS isvip \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN chat_visitor cv ON cr.client_id = cv.visitor_guid \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") r3 ON r1.chat_session_id = r3.chat_session_id\n" +
                    "LEFT JOIN ( SELECT id, cs.start_time, cs.end_time FROM chat_session cs WHERE 1 = 1 \n" +
                    " and cs.start_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cs.start_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    ") r4 ON r1.chat_session_id = r4.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "CASE\n" +
                    "WHEN cr.request_status_id = 12 THEN\n" +
                    "to_chat_user_id ELSE NULL \n" +
                    "END AS robotid,\n" +
                    "CASE\n" +
                    "WHEN cr.request_status_id = 12 THEN\n" +
                    "( agent_end_time - end_time )/ 1000 ELSE NULL \n" +
                    "END robottime \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "AND length( from_chat_user_id )> 6 \n" +
                    "ORDER BY\n" +
                    "1 DESC \n" +
                    ") r5 ON r1.chat_session_id = r5.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr1.chat_session_id,\n" +
                    "CASE\n" +
                    "WHEN cr1.to_chat_user_id LIKE 'BOT%' THEN\n" +
                    "'否' ELSE '是' \n" +
                    "END AS isagent,\n" +
                    "CASE\n" +
                    "WHEN cr1.to_chat_user_id IS NULL THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS agent_is_giveup \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id FROM chat_request cr WHERE \n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    " ORDER BY chat_session_id ASC, creation_time DESC ) t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 ON cr1.chat_session_id = cr.chat_session_id \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "ORDER BY\n" +
                    "1 DESC \n" +
                    ") r6 ON r1.chat_session_id = r6.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr1.chat_session_id,\n" +
                    "cr1.creation_time,\n" +
                    "( cr1.end_time - cr1.creation_time )/ 1000 AS agent_wait_time \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "end_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "end_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "( SELECT chat_session_id, creation_time, end_time, queue_id, to_chat_user_id FROM chat_request cr WHERE \n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    " ORDER BY chat_session_id ASC, creation_time DESC ) t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 \n" +
                    "\n" +
                    ") r7 ON r1.chat_session_id = r7.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "count( CASE WHEN length( chat_user_id )= 6 AND reply_delay > 0 THEN id ELSE NULL END ) AS custtotal,\n" +
                    "sum( CASE WHEN length( chat_user_id )= 6 AND reply_delay > 0 THEN reply_delay ELSE 0 END ) AS response_time \n" +
                    "FROM\n" +
                    "chat_message cm \n" +
                    "WHERE\n" +
                    "cm.Post_Time >=UNIX_TIMESTAMP('"+starttime+"')*1000 And cm.Post_Time <=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "GROUP BY\n" +
                    "chat_session_id \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") r8 ON r1.chat_session_id = r8.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr.chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN clm.chat_request_id IS NOT NULL THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS isleave_msg \n" +
                    "FROM\n" +
                    "chat_leave_msg clm\n" +
                    "LEFT JOIN chat_request cr ON cr.id = clm.chat_request_id \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") r9 ON r1.chat_session_id = r9.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr1.chat_session_id,\n" +
                    "cr1.to_chat_user_id \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id FROM chat_request cr WHERE \n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    " ORDER BY chat_session_id ASC, creation_time DESC ) t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 ON cr1.chat_session_id = cr.chat_session_id \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr1.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    ") A ON r1.chat_session_id = a.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.chat_session_id,\n" +
                    "max( post_time ) AS maxtime \n" +
                    "FROM\n" +
                    "chat_message cm \n" +
                    "WHERE\n" +
                    "cm.Post_Time >=UNIX_TIMESTAMP('"+starttime+"')*1000 And cm.Post_Time <=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "AND length( cm.chat_user_id )= 6 \n" +
                    "AND chat_user_id NOT LIKE 'BOT%' \n" +
                    "GROUP BY\n" +
                    "cm.chat_session_id \n" +
                    ") B ON a.chat_session_id = b.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "sum( CASE WHEN request_type_id = 3 THEN 1 ELSE 0 END ) AS istransfer \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    "HAVING\n" +
                    "sum( CASE WHEN request_type_id = 3 THEN 1 ELSE 0 END ) > 0 \n" +
                    ") C ON a.chat_session_id = c.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr1.chat_session_id,\n" +
                    "( cr1.agent_end_time - cr1.end_time ) / 1000 AS rg_service_duration \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time FROM chat_request cr WHERE \n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    " ORDER BY chat_session_id ASC, creation_time DESC ) t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 \n" +
                    "WHERE\n" +
                    "1 = 1 \n" +
                    "AND ( cr1.to_chat_user_id NOT LIKE 'BOT%' AND cr1.to_chat_user_id IS NOT NULL ) \n" +
                    ") D ON a.chat_session_id = d.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.Chat_Session_Id AS Session_Id,\n" +
                    "count( DISTINCT cm.id ) AS 'cust_zrghsl' \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id \n" +
                    "AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cm.post_time >= cr.creation_time \n" +
                    "AND LENGTH( chat_user_id )= 36 \n" +
                    "AND message_type = 0 \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "AND cm.chat_session_id IS NOT NULL \n" +
                    "GROUP BY\n" +
                    "cm.Chat_Session_Id \n" +
                    ") E ON a.chat_session_id = e.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.Chat_Session_Id AS Session_Id,\n" +
                    "max( reply_delay ) maxtotal,\n" +
                    "min( CASE WHEN reply_delay > 0 THEN reply_delay END ) mintotal \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id \n" +
                    "AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cm.post_time >= cr.creation_time \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "AND length( cm.chat_user_id ) = 6 \n" +
                    "AND cm.chat_user_id NOT LIKE 'BOT%' \n" +
                    "GROUP BY\n" +
                    "cm.Chat_Session_Id \n" +
                    "ORDER BY\n" +
                    "Session_Id DESC \n" +
                    ") F ON a.chat_session_id = f.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.Chat_Session_Id AS Session_Id,\n" +
                    "count( DISTINCT cm.id ) AS 'zx_send_sl' \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id \n" +
                    "AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cm.post_time >= cr.creation_time \n" +
                    "AND LENGTH( chat_user_id )= 6 \n" +
                    "AND message_type = 0 \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "AND cm.chat_session_id IS NOT NULL \n" +
                    "GROUP BY\n" +
                    "cm.Chat_Session_Id \n" +
                    ") G ON a.chat_session_id = g.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cs.id,\n" +
                    "sum( CASE WHEN push_rating_by IS NOT NULL THEN 1 ELSE 0 END ) '是否邀评' \n" +
                    "FROM\n" +
                    "chat_session cs \n" +
                    "WHERE\n" +
                    "cs.start_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cs.start_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "GROUP BY\n" +
                    "cs.id \n" +
                    ") H ON a.chat_session_id = h.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cs.id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN ( cs.rating_id IS NOT NULL OR cs.rating_id <> '' ) THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS '是否评价',\n" +
                    "cs.rating_id AS '评价满意度' \n" +
                    "FROM\n" +
                    "chat_session cs \n" +
                    "WHERE\n" +
                    "cs.start_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cs.start_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    ") I ON a.chat_session_id = i.id\n" +
                    "LEFT JOIN staff s ON a.to_chat_user_id = s.staff_id\n" +
                    "LEFT JOIN (-- 在线请求数\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "count( DISTINCT CASE WHEN cr.request_status_id != 6 THEN cr.chat_session_id ELSE NULL END ) AS 在线请求数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "where cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") J ON r1.chat_session_id = j.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "sum( DISTINCT CASE WHEN cr.request_status_id = 7 THEN 1 ELSE 0 END ) AS 访客放弃数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "where cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000\n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") K ON r1.chat_session_id = k.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )<= 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 未等待应答数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "  cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "and ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") L ON r1.chat_session_id = l.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "and ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") M ON r1.chat_session_id = m.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "sum( t.未等待应答数 + t.等待应答数 ) AS 在线应答数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )<= 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 未等待应答数,\n" +
                    "count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "and ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") t \n" +
                    "GROUP BY\n" +
                    "t.chat_session_id \n" +
                    ") N ON r1.chat_session_id = n.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "count(\n" +
                    "DISTINCT\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN ( cr.end_time - cr.creation_time )> 5000 \n" +
                    "AND request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NOT NULL THEN\n" +
                    "cr.CHAT_SESSION_ID \n" +
                    "WHEN cr.request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NULL THEN\n" +
                    "cr.id ELSE NULL \n" +
                    "END \n" +
                    ") AS 等待放弃数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "and ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") O ON r1.chat_session_id = o.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "sum( CASE WHEN cr.request_status_id = 5 THEN 1 ELSE 0 END ) 等待转留言数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "and ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") P ON r1.chat_session_id = p.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "t.id,\n" +
                    "sum( t.等待放弃数 + t.等待应答数 + t.等待转留言数 ) AS 总等待数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "cr.id,\n" +
                    "count(\n" +
                    "DISTINCT\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN ( cr.end_time - cr.creation_time )> 5000 \n" +
                    "AND request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NOT NULL THEN\n" +
                    "cr.CHAT_SESSION_ID \n" +
                    "WHEN cr.request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NULL THEN\n" +
                    "cr.id ELSE NULL \n" +
                    "END \n" +
                    ") AS 等待放弃数,\n" +
                    "count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数,\n" +
                    "sum( CASE WHEN cr.request_status_id = 5 THEN 1 ELSE 0 END ) 等待转留言数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "and((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.id \n" +
                    ") t \n" +
                    "GROUP BY\n" +
                    "t.id \n" +
                    ") Q ON r1.id = q.id\n" +
                    "LEFT JOIN (\n" +
                    "select \n" +
                    "cr.chat_session_id,\n" +
                    "count( DISTINCT CASE WHEN cr.request_status_id = 5 THEN cr.chat_session_id ELSE NULL END ) AS 离线请求数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "where cr.creation_time>= UNIX_TIMESTAMP('"+starttime+"')*1000 and cr.creation_time<=UNIX_TIMESTAMP('"+endtime+"')*1000 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") S ON r1.chat_session_id = s.chat_session_id \n" +
                    "ORDER BY\n" +
                    "r1.id ASC ";*/
            List selectList = eliteBean.chaxun_sql(selectSql, "dataSource");
            String[] doSaveResultSQL = new String[selectList.size()];
            log.info("手动汇总网聊数据："+selectList.toString());

            int k = 0;
            for (int i = 0; i < selectList.size(); i++) {

                Map selectMap = selectList.get(i);
                String rec_guid = isnull(selectMap.get("aaaaaaa").toString());
                String chat_session_id = isnull(selectMap.get("chat_session_id").toString());
                String id = isnull(selectMap.get("id").toString());
                String client_id = isnull(selectMap.get("client_id").toString());
                String queue_id = isnull(selectMap.get("queue_id").toString());
                String isvip = isnull(selectMap.get("isvip").toString());
                String start_time = isnull(selectMap.get("start_time").toString());
                String end_time = isnull(selectMap.get("end_time").toString());
                String robotid = isnull(selectMap.get("robotid").toString());
                String robottime = isnull(selectMap.get("robottime").toString());
                String isagent = isnull(selectMap.get("isagent").toString());
                String agent_is_giveup = isnull(selectMap.get("agent_is_giveup").toString());
                String creation_time = isnull(selectMap.get("creation_time").toString());
                String agent_wait_time = isnull(selectMap.get("agent_wait_time").toString());
                String isleave_msg = isnull(selectMap.get("isleave_msg").toString());
                String custtotal = isnull(selectMap.get("custtotal").toString());
                String response_time = isnull(selectMap.get("response_time").toString());
                String staff_id = isnull(selectMap.get("staff_id").toString());
                String maxtime = isnull(selectMap.get("maxtime").toString());
                String istransfer = isnull(selectMap.get("istransfer").toString());
                String zj_chat_session_id = isnull(selectMap.get("zj_chat_session_id").toString());
                String rg_service_duration = isnull(selectMap.get("rg_service_duration").toString());
                String cust_zrghsl = isnull(selectMap.get("cust_zrghsl").toString());
                String maxtotal = isnumber(selectMap.get("maxtotal").toString());
                String mintotal = isnumber(selectMap.get("mintotal").toString());
                String zx_send_sl = isnumber(selectMap.get("zx_send_sl").toString());
                String isyp = isnull(selectMap.get("isyp").toString());
                String israting = isnull(selectMap.get("israting").toString());
                String rating = isnull(selectMap.get("rating").toString());
                String zxnum = isnumber(selectMap.get("zxnum").toString());
                String fknum = isnumber(selectMap.get("fknum").toString());
                String notwaitnum = isnumber(selectMap.get("notwaitnum").toString());
                String waitnum = isnumber(selectMap.get("waitnum").toString());
                String zxydnum = isnumber(selectMap.get("zxydnum").toString());
                String waitfqnum = isnumber(selectMap.get("waitfqnum").toString());
                String waitlynum = isnumber(selectMap.get("waitlynum").toString());
                String waittotal = isnumber(selectMap.get("waittotal").toString());
                String lxnum = isnumber(selectMap.get("lxnum").toString());



                String selectCheck = "select request_id from chat_service_detail where DATE_FORMAT(createddate,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') and request_id = " + id;

                List selectCheckList = eliteBean.chaxun_sql(selectCheck, "dataSource");
                if (selectCheckList.size() > 0) {

                    for (int j = 0; j < selectCheckList.size(); j++) {
                        Map selectCheckMap = selectCheckList.get(j);
                        String m_ID = selectCheckMap.get("request_id");
                        // 更新当前时间 记录
                        String upSql = "Update chat_service_detail" +
                                "   Set session_id     = '" + chat_session_id + "'," +
                                "       queue_id     = '" + queue_id + "'," +
                                "       client_id     = '" + client_id + "'," +
                                "       isvip     = '" + isvip + "'," +
                                "       session_starttime     = '" + start_time + "'," +
                                "       session_endtime     = '" + end_time + "'," +
                                "       robot_id     = '" + robotid + "'," +
                                "       robot_time         = '" + robottime + "'," +
                                "       isagent         = '" + isagent + "'," +
                                "       agent_starttime      = '" + creation_time + "'," +
                                "       agent_wait_time      = '" + agent_wait_time + "'," +
                                "       agent_isgiveup      = '" + agent_is_giveup + "'," +
                                "       is_LEAVE_MSG      = '" + isleave_msg + "'," +
                                "       staff_id  = '" + staff_id + "'," +
                                "       agent_end_time = '" + maxtime + "'," +
                                "       istransfer = '" + istransfer + "'," +
                                "       transfer_session_id    = '" + zj_chat_session_id + "'," +
                                "       agent_time    = '" + rg_service_duration + "'," +
                                "       custtotal    = '" + cust_zrghsl + "'," +
                                "       max_response_time    = '" + maxtotal + "'," +
                                "       min_response_time    = '" + mintotal + "'," +
                                "       agenttotal    = '" + zx_send_sl + "'," +
                                "       is_Invite_comments    = '" + isyp + "'," +
                                "       is_evaluate    = '" + israting + "'," +
                                "       rating    = '" + rating + "'," +
                                "       cyclesnum    = '" + custtotal + "'," +
                                "       response_time    = '" + response_time + "'," +
                                "       request_online    = '" + zxnum + "'," +
                                "       visitor_give_up    = '" + fknum + "'," +
                                "       no_wait_response    = '" + notwaitnum + "'," +
                                "       wait_response    = '" + waitnum + "'," +
                                "       online_response    = '" + zxydnum + "'," +
                                "       wait_give_up    = '" + waitfqnum + "'," +
                                "       wait_to_message    = '" + waitlynum + "'," +
                                "       total_wait    = '" + waittotal + "'," +
                                "       request_offline    = '" + lxnum + "'" +
                                " Where request_id = '" + m_ID + "'";
                        log.debug('更新当前时间的记录SQL' + upSql);
                        doSaveResultSQL[i] = upSql;
                    }
                } else {

                    // 插入该条记录
                    String insertSql = "Insert Into chat_service_detail" +
                            "  (rec_guid," +
                            "   request_id," +
                            "   session_id," +
                            "   queue_id," +
                            "   client_id," +
                            "   isvip," +
                            "   session_starttime," +
                            "   session_endtime," +
                            "   robot_id," +
                            "   robot_time," +
                            "   isagent," +
                            "   agent_starttime," +
                            "   agent_wait_time," +
                            "   agent_isgiveup," +
                            "   is_LEAVE_MSG," +
                            "   staff_id," +
                            "   agent_end_time," +
                            "   istransfer," +
                            "   transfer_session_id," +
                            "   agent_time," +
                            "   custtotal," +
                            "   max_response_time," +
                            "   min_response_time," +
                            "   agenttotal," +
                            "   is_Invite_comments," +
                            "   is_evaluate," +
                            "   rating," +
                            "   cyclesnum," +
                            "   response_time," +
                            "   request_online," +
                            "   visitor_give_up," +
                            "   no_wait_response," +
                            "   wait_response," +
                            "   online_response," +
                            "   wait_give_up," +
                            "   wait_to_message," +
                            "   total_wait," +
                            "   request_offline," +
                            "   createddate)" +
                            " Values" +
                            "  ('" + rec_guid + "'," +
                            "   '" + id + "'," +
                            "   '" + chat_session_id + "'," +
                            "   '" + queue_id + "'," +
                            "   '" + client_id + "'," +
                            "   '" + isvip + "'," +
                            "   '" + start_time + "'," +
                            "   '" + end_time + "'," +
                            "   '" + robotid + "'," +
                            "   '" + robottime + "'," +
                            "   '" + isagent + "'," +
                            "   '" + creation_time + "'," +
                            "   '" + agent_wait_time + "'," +
                            "   '" + agent_is_giveup + "'," +
                            "   '" + isleave_msg + "'," +
                            "   '" + staff_id + "'," +
                            "   '" + maxtime + "'," +
                            "   '" + istransfer + "'," +
                            "   '" + zj_chat_session_id + "'," +
                            "   '" + rg_service_duration + "'," +
                            "   '" + cust_zrghsl + "'," +
                            "   '" + maxtotal + "'," +
                            "   '" + mintotal + "'," +
                            "   '" + zx_send_sl + "'," +
                            "   '" + isyp + "'," +
                            "   '" + israting + "'," +
                            "   '" + rating + "'," +
                            "   '" + custtotal + "'," +
                            "   '" + response_time + "'," +
                            "   '" + zxnum + "'," +
                            "   '" + fknum + "'," +
                            "   '" + notwaitnum + "'," +
                            "   '" + waitnum + "'," +
                            "   '" + zxydnum + "'," +
                            "   '" + waitfqnum + "'," +
                            "   '" + waitlynum + "'," +
                            "   '" + waittotal + "'," +
                            "   '" + lxnum + "'," +
                            "   Now())";
                    log.debug('插入当前时间的记录SQL' + insertSql);
                    doSaveResultSQL[i] = insertSql;

                }

                k = k + 1;
            }

            if (k > 0) {
                List doSaveResultSQLList = eliteBean.updateBatch(doSaveResultSQL, "dataSource");
                log.debug("网聊数据定时任务处理End-耗时:" + (System.currentTimeMillis() - endTime) + " 结果:" + doSaveResultSQLList.toString());
            } else {

                log.warn("网聊数据定时任务处理End...SQL空");
            }

            log.debug("网聊数据定时任务处理结束-总耗时:" + (System.currentTimeMillis() - startTime));


        } catch (Exception e) {
            log.error("网聊数据定时任务操作异常", e);
        }
    }

    // 非空校验isnumber
    public static String isnumber(String key) {

        String reResult = "";
        if (key == "" || key.length() <= 0 || key == null) {
            reResult = 0;
        } else {
            reResult = key;
        }
        return reResult;
    }

    // 非空校验isnull
    public static String isnull(String key) {

        String reResult = "";
        if (key == "" || key.length() <= 0 || key == null) {
            reResult = "";
        } else {
            reResult = key;
        }
        return reResult;
    }

}

